﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace MCPro
{
    #region Enumerations

    /// <summary>
    /// List of all Data Server
    /// </summary>
    public enum ConnType
    {
        AppConfig,
        ConnString
    }

    #endregion Enumerations

    #region Structures

    /// <summary>
    /// List of all SqlClient command fields
    /// </summary>
    struct sqlFields
    {
        public SqlConnection conn;
        public SqlCommand cmd;
        public SqlDataAdapter da;
        public SqlDataReader dr;
        public DataTable dt;
        public DataSet ds;
        public DataRow row;
    }

    #endregion Structures

    #region Classes

    /// <summary>
    /// DataServer Main class
    /// </summary>
    public class DataServer
    {
        //Connection Method
        /// <summary>
        /// DataServer connection procedure using enum value
        /// </summary>
        /// <param name="ConnString">Use the ServerDB Enum under the DataServer class to get the list of the servers</param>
        private SqlConnection Connect(string connConnString)
        {
            sqlFields dataFields;
            dataFields.conn = new SqlConnection(connConnString);

            dataFields.conn.Open();
            return dataFields.conn;
        }


        //Select Methods
        /// <summary>
        /// DataServer select all fields from the given table and pass to a DataTable
        /// </summary>
        /// <param name="ConnString">enum Server Name</param>
        /// <param name="TableName">DataServer Table Name</param>
        public DataTable Select(string ConnString, string TableName)
        {
            DataServer clsData = new DataServer();
            SqlConnection ServerConn = new SqlConnection();
            ServerConn = clsData.Connect(ConnString);

            sqlFields dataFields;

            dataFields.cmd = new SqlCommand("Select * From " + TableName, ServerConn);
            dataFields.dr = dataFields.cmd.ExecuteReader();
            dataFields.dt = new DataTable();

            dataFields.dt.BeginLoadData();
            dataFields.dt.Load(dataFields.dr);
            dataFields.dt.EndLoadData();

            dataFields.cmd.Dispose();
            ServerConn.Close();

            return dataFields.dt;
        }

        /// <summary>
        /// DataServer select all fields from the given table and pass to a DataTable
        /// </summary>
        /// <param name="ConnString">enum Server Name</param>
        /// <param name="TableName">DataServer Table Name</param>
        /// <param name="Condition">DataServer Condition</param>
        public DataTable Select(string ConnString, string TableName, string Condition)
        {
            DataServer clsData = new DataServer();
            SqlConnection ServerConn = new SqlConnection();
            ServerConn = clsData.Connect(ConnString);

            sqlFields dataFields;

            dataFields.cmd = new SqlCommand("Select * From " + TableName + " Where " + Condition, ServerConn);
            dataFields.dr = dataFields.cmd.ExecuteReader();
            dataFields.dt = new DataTable();

            dataFields.dt.BeginLoadData();
            dataFields.dt.Load(dataFields.dr);
            dataFields.dt.EndLoadData();

            dataFields.cmd.Dispose();
            ServerConn.Close();

            return dataFields.dt;
        }

        /// <summary>
        /// DataServer select distinct given fields from the given table and pass to a DataTable
        /// </summary>
        /// <param name="ConnString">enum Server Name</param>
        /// <param name="TableName">DataServer Table Name</param>
        /// <param name="FieldNames">Collection of field names from an array string</param>
        /// <param name="Distinct">Set query it it will do a distinct or not</param>
        public DataTable Select(string ConnString, string TableName, string[] FieldNames, bool Distinct)
        {
            DataServer clsData = new DataServer();
            SqlConnection ServerConn = new SqlConnection();
            ServerConn = clsData.Connect(ConnString);

            sqlFields dataFields;

            string CompoundFields = "";
            string SelectValue = "";

            if (Distinct == true) { SelectValue = "Select Distinct "; }
            else { SelectValue = "Select "; }

            for (int i = 0; i < FieldNames.Length; i++)
            {
                if (i == FieldNames.Length - 1)
                { CompoundFields += FieldNames[i]; }
                else
                { CompoundFields += FieldNames[i] + ","; }
            }

            dataFields.cmd = new SqlCommand(SelectValue + CompoundFields + " From " + TableName, ServerConn);
            dataFields.dr = dataFields.cmd.ExecuteReader();
            dataFields.dt = new DataTable();

            dataFields.dt.BeginLoadData();
            dataFields.dt.Load(dataFields.dr);
            dataFields.dt.EndLoadData();

            dataFields.cmd.Dispose();
            ServerConn.Close();

            return dataFields.dt;
        }

        /// <summary>
        /// DataServer select distinct given fields from the given table and pass to a DataTable
        /// </summary>
        /// <param name="ConnString">enum Server Name</param>
        /// <param name="TableName">DataServer Table Name</param>
        /// <param name="FieldNames">Collection of field names from an array string</param>
        /// <param name="Condition">DataServer Condition</param>
        /// <param name="Distinct">Set query it it will do a distinct or not</param>
        public DataTable Select(string ConnString, string TableName, string[] FieldNames, string Condition, bool Distinct)
        {
            DataServer clsData = new DataServer();
            SqlConnection ServerConn = new SqlConnection();
            ServerConn = clsData.Connect(ConnString);

            sqlFields dataFields;

            string CompoundFields = "";
            string SelectValue = "";

            if (Distinct == true)
            { SelectValue = "Select Distinct "; }
            else
            { SelectValue = "Select "; }

            for (int i = 0; i < FieldNames.Length; i++)
            {
                if (i == FieldNames.Length - 1)
                { CompoundFields += FieldNames[i]; }
                else
                { CompoundFields += FieldNames[i] + ","; }
            }

            dataFields.cmd = new SqlCommand(SelectValue + CompoundFields + " From " + TableName + " Where " + Condition, ServerConn);
            dataFields.dr = dataFields.cmd.ExecuteReader();
            dataFields.dt = new DataTable();

            dataFields.dt.BeginLoadData();
            dataFields.dt.Load(dataFields.dr);
            dataFields.dt.EndLoadData();

            dataFields.cmd.Dispose();
            ServerConn.Close();

            return dataFields.dt;
        }

        /// <summary>
        /// DataServer custom select command and pass to a DataTable
        /// </summary>
        /// <param name="ConnString">enum Server Name</param>
        /// <param name="CustomCommand">Custom SqlCommand</param>
        public DataTable Select(string ConnString, SqlCommand CustomCommand)
        {
            DataServer clsData = new DataServer();
            SqlConnection ServerConn = new SqlConnection();
            ServerConn = clsData.Connect(ConnString);

            sqlFields dataFields;

            dataFields.cmd = new SqlCommand(CustomCommand.CommandText, ServerConn);
            dataFields.dr = dataFields.cmd.ExecuteReader();
            dataFields.dt = new DataTable();

            dataFields.dt.BeginLoadData();
            dataFields.dt.Load(dataFields.dr);
            dataFields.dt.EndLoadData();

            dataFields.cmd.Dispose();
            ServerConn.Close();

            return dataFields.dt;
        }


        //Insert Method
        /// <summary>
        /// DataServer insert given values to the given fields from the given table
        /// </summary>
        /// <param name="ConnString">enum Server Name</param>
        /// <param name="TableName">DataServer Table Name</param>
        /// <param name="Fields">Collection of fields from an array string</param>
        /// <param name="Values">Collection of values from an array string</param>
        public void Insert(string ConnString, string TableName, string[] Fields, string[] Values)
        {
            DataServer clsData = new DataServer();
            SqlConnection ServerConn = new SqlConnection();
            ServerConn = clsData.Connect(ConnString);

            sqlFields dataFields;

            string CompoundFields = "";
            string CompoundValues = "";

            for (int i = 0; i < Fields.Length; i++)
            {
                if (i == Fields.Length - 1)
                { CompoundFields += Fields[i]; }
                else
                { CompoundFields += Fields[i] + ","; }
            }

            for (int i = 0; i < Values.Length; i++)
            {
                if (i == Values.Length - 1)
                { CompoundValues += Values[i]; }
                else
                { CompoundValues += Values[i] + ","; }
            }

            dataFields.cmd = new SqlCommand("Insert Into " + TableName + " (" + CompoundFields + ") Values (" + CompoundValues + ")", ServerConn);
            dataFields.cmd.ExecuteNonQuery();
            ServerConn.Close();
        }

        /// <summary>
        /// DataServer custom insert command
        /// </summary>
        /// <param name="ConnString">enum Server Name</param>
        /// <param name="CustomCommand">Custom SqlCommand</param>
        public void Insert(string ConnString, SqlCommand CustomCommand)
        {
            DataServer clsData = new DataServer();
            SqlConnection ServerConn = new SqlConnection();
            ServerConn = clsData.Connect(ConnString);

            sqlFields dataFields;

            dataFields.cmd = new SqlCommand(CustomCommand.CommandText, ServerConn);
            dataFields.cmd.ExecuteNonQuery();
            ServerConn.Close();
        }

        //Delete Methods
        /// <summary>
        /// DataServer delete record(s) base on the given table and condition
        /// </summary>
        /// <param name="ConnString">enum Server Name</param>
        /// <param name="TableName">DataServer Table Name</param>
        /// <param name="Condition">DataServer Condition</param>
        public void Delete(string ConnString, string TableName, string Condition)
        {
            DataServer clsData = new DataServer();
            SqlConnection ServerConn = new SqlConnection();
            ServerConn = clsData.Connect(ConnString);

            sqlFields dataFields;

            dataFields.cmd = new SqlCommand("Delete From " + TableName + " Where " + Condition, ServerConn);
            dataFields.cmd.ExecuteNonQuery();
            ServerConn.Close();
        }

        /// <summary>
        /// DataServer custom delete command
        /// </summary>
        /// <param name="ConnString">enum Server Name</param>
        /// <param name="CustomCommand">Custom SqlCommand</param>
        public void Delete(string ConnString, SqlCommand CustomCommand)
        {
            DataServer clsData = new DataServer();
            SqlConnection ServerConn = new SqlConnection();
            ServerConn = clsData.Connect(ConnString);

            sqlFields dataFields;

            dataFields.cmd = new SqlCommand(CustomCommand.CommandText, ServerConn);
            dataFields.cmd.ExecuteNonQuery();
            ServerConn.Close();
        }


        //Update Methods
        /// <summary>
        /// DataServer update record(s) base on the given table, fields from a string of array,
        /// values from a string of arrays and condition
        /// </summary>
        /// <param name="ConnString">DataServer Server Name</param>
        /// <param name="TableName">DataServer Table Name</param>
        /// <param name="Fields">Collection of fields from a string of array</param>
        /// <param name="Values">Collection of values from a string of array</param>
        /// <param name="Condition">DataServer Condition</param>
        public void Update(string ConnString, string TableName, string[] Fields, string[] Values, string Condition)
        {
            DataServer clsData = new DataServer();
            SqlConnection ServerConn = new SqlConnection();
            ServerConn = clsData.Connect(ConnString);

            sqlFields dataFields;

            string CompoundFieldswithValues = "";

            if (Fields.Length == Values.Length)
            {
                for (int i = 0; i < Values.Length; i++)
                {
                    if (i == Values.Length - 1)
                    { CompoundFieldswithValues += Fields[i] + " = " + Values[i]; }
                    else
                    { CompoundFieldswithValues += Fields[i] + " = " + Values[i] + ","; }
                }
            }
            else { throw new ArgumentException("Number of query values and destination fields are not the same"); }

            dataFields.cmd = new SqlCommand("Update " + TableName + " Set " + CompoundFieldswithValues + " Where " + Condition, ServerConn);
            dataFields.cmd.ExecuteNonQuery();
            ServerConn.Close();
        }

        /// <summary>
        /// DataServer custom update command
        /// </summary>
        /// <param name="ConnString">enum Server Name</param>
        /// <param name="CustomCommand">Custom SqlCommand</param>
        public void Update(string ConnString, SqlCommand CustomCommand)
        {
            DataServer clsData = new DataServer();
            SqlConnection ServerConn = new SqlConnection();
            ServerConn = clsData.Connect(ConnString);

            sqlFields dataFields;

            dataFields.cmd = new SqlCommand(CustomCommand.CommandText, ServerConn);
            dataFields.cmd.ExecuteNonQuery();
            ServerConn.Close();
        }

        /// <summary>
        /// DataServer stored procedure to process data
        /// Returns: @@Error value
        /// </summary>
        /// <param name="ConnString">enum Server Name</param>
        /// <param name="StoredProcedure">DataServer stored proc</param>
        public int StoredProc(string ConnString, string StoredProcedure)
        {
            int RetVal = 0;
            DataServer clsData = new DataServer();
            SqlConnection ServerConn = new SqlConnection();
            ServerConn = clsData.Connect(ConnString);

            sqlFields dataFields;

            dataFields.cmd = new SqlCommand(StoredProcedure, ServerConn);
            dataFields.cmd.CommandType = CommandType.StoredProcedure;

            dataFields.dr = dataFields.cmd.ExecuteReader();
            ServerConn.Close();
            dataFields.dr.Close();

            return RetVal;
        }

        /// <summary>
        /// DataServer stored proc with parameter(s) to process data. The receiving param name from stored proc should be equal to the array fields.
        /// Returns: @@Error value
        /// </summary>
        /// <param name="ConnString">enum Server Name</param>
        /// <param name="StoredProcedure">DataServer stored proc</param>
        /// <param name="Fields">Collection of fields from a string of array</param>
        public int StoredProc(string ConnString, string StoredProcedure, string[] Fields)
        {
            int RetVal = 0;
            DataServer clsData = new DataServer();
            SqlConnection ServerConn = new SqlConnection();
            ServerConn = clsData.Connect(ConnString);

            sqlFields dataFields;

            dataFields.cmd = new SqlCommand(StoredProcedure, ServerConn);
            dataFields.cmd.CommandType = CommandType.StoredProcedure;

            for (int i = 0; i < Fields.Length; i++)
            {
                dataFields.cmd.Parameters.Add(new SqlParameter("@" + Fields[i], Fields[i]));
            }

            dataFields.dr = dataFields.cmd.ExecuteReader();
            ServerConn.Close();
            dataFields.dr.Close();

            return RetVal;
        }
    }

    #endregion Classes
}